Homework 5 - Documenting an HR database

Author

Christopher Torres

Published

April 6, 2025

Thank you for hiring me to take on this role of working directly on you database! After having received credentials to the HR database, I am ready to start revising it to better fit our needs! In this report, I am going to create:

  1. A general written description of contents of database.

  2. A Chen diagram highlighting the major entities and relations.

  3. A Crows-foot diagram highlighting the entities, attributes, and the cardinality and participation of the relations.

  4. A python-generated listing of SQL tables and fields, and a listing of sample data showing the first bunch (5 or 10 or so) of EACH table.

  5. A HTML report, which will be uploaded to canvas.

Github repo: https://github.com/cmsc-vcu/cmsc408-sp2025-hw5-serrotrehpotsirhc.git

Database design

The following sections document the general design of the HR database.

General description

This database represents the Human Resources of the fortune company I have been hired by! There are multiple sections of data for each employee managed by HR. These sections include:

  1. Countries
  2. Departments
  3. Employees
  4. Emp_details_view
  5. Jobs
  6. Job_grades
  7. Job_history
  8. Locations
  9. Regions.

The purpose of this database is to manage each employee and their relevant data. The major tables are listed above.

Hierarchical Relationships

Regions contain countries.

Countries contain locations.

Locations house departments.

Departments employ workers.

Employees have managers, creating a reporting structure.

The purpose of this database is to manage its employees and store their relevant data.

Organizational Structure Tables

Departments Table

Stores department data:

  1. Department ID

  2. Department name

  3. Manager ID

  4. Location ID

Locations Table

Stores the location of company departments:

  1. Location ID

  2. Street address

  3. Postal code

  4. City

  5. State/Province

  6. Country ID

Countries Table

Stores data of countries where the company operates:

  1. Country ID

  2. Country name

  3. Region ID

Regions Table

Stores data of regions:

  1. Region ID

  2. Region name

Chen diagram

The following diagram highlights the major relations in the database.

graph ER {
    fontname="Helvetica,Arial,sans-serif"
    fontsize=30;
    bgcolor=transparent;

    layout=neato  
    scale=1.2     

    node [fontname="Helvetica,Arial,sans-serif",fontsize=10]

    // Define nodes
    node [shape=box];
    employee; department; job; job_history; job_grade; location; country; region;
    
    node [shape=ellipse];
    employee_id; first_name; last_name; email; phone_number; hire_date; job_id; salary; commission_pct; manager_id; department_id;
    department_id; department_name; location_id; manager_id;
    job_id; job_title; min_salary; max_salary;
    grade_level; lowest_sal; highest_sal;
    location_id; street_address; postal_code; city; state_province; country_id;
    country_id; country_name; region_id;
    region_id; region_name;

    
    node [shape=diamond,style=filled,color=lightgrey]; 
    "Works_In"; "Located_In"; "Part_Of"; "Has_Job"; "History"; "Graded";

    // Define edges
    employee -- employee_id;
    employee -- first_name;
    employee -- last_name;
    employee -- email;
    employee -- phone_number;
    employee -- hire_date;
    employee -- job_id;
    employee -- salary;
    employee -- commission_pct;
    employee -- manager_id;
    employee -- department_id;
    employee -- "Works_In" [label="m"];
    "Works_In" -- department [label="1"];

    department -- department_id;
    department -- department_name;
    department -- location_id;
    department -- manager_id;
    department -- "Located_In" [label="1"];
    "Located_In" -- location [label="1"];

    location -- location_id;
    location -- street_address;
    location -- postal_code;
    location -- city;
    location -- state_province;
    location -- country_id;
    location -- "Part_Of" [label="1"];
    "Part_Of" -- country [label="1"];

    country -- country_id;
    country -- country_name;
    country -- region_id;

    country -- "Part_Of" [label="1"];
    "Part_Of" -- region [label="1"];
    
    region -- region_id;
    region -- region_name;

    employee -- "Has_Job" [label="1"];
    "Has_Job" -- job [label="1"];

    job -- job_id;
    job -- job_title;
    job -- min_salary;
    job -- max_salary;
    job -- "Graded" [label="1"];
    "Graded" -- job_grade [label="1"];

    job_grade -- grade_level;
    job_grade -- lowest_sal;
    job_grade -- highest_sal;

    employee -- "History" [label="m"];
    "History" -- job_history [label="1"];

    job_history -- job_id;
    job_history -- department_id;
    job_history -- employee_id;
    job_history -- start_date;
    job_history -- end_date;
}

ER employee employee employee_id employee_id employee--employee_id first_name first_name employee--first_name last_name last_name employee--last_name email email employee--email phone_number phone_number employee--phone_number hire_date hire_date employee--hire_date job_id job_id employee--job_id salary salary employee--salary commission_pct commission_pct employee--commission_pct manager_id manager_id employee--manager_id department_id department_id employee--department_id Works_In Works_In employee--Works_In m Has_Job Has_Job employee--Has_Job 1 History History employee--History m department department department--manager_id department--department_id department_name department_name department--department_name location_id location_id department--location_id Located_In Located_In department--Located_In 1 job job job--job_id job_title job_title job--job_title min_salary min_salary job--min_salary max_salary max_salary job--max_salary Graded Graded job--Graded 1 job_history job_history job_history--employee_id job_history--job_id job_history--department_id start_date start_date job_history--start_date end_date end_date job_history--end_date job_grade job_grade grade_level grade_level job_grade--grade_level lowest_sal lowest_sal job_grade--lowest_sal highest_sal highest_sal job_grade--highest_sal location location location--location_id street_address street_address location--street_address postal_code postal_code location--postal_code city city location--city state_province state_province location--state_province country_id country_id location--country_id Part_Of Part_Of location--Part_Of 1 country country country--country_id country_name country_name country--country_name region_id region_id country--region_id country--Part_Of 1 region region region--region_id region_name region_name region--region_name Works_In--department 1 Located_In--location 1 Part_Of--country 1 Part_Of--region 1 Has_Job--job 1 History--job_history 1 Graded--job_grade 1

Crows foot diagram

The following diagram provides greater detail on the entities and attributes and highlights the cardinality and participation of the relations.

erDiagram
    EMPLOYEE {
        int employeeID
        string first_name
        string last_name
        string email
        string phone_number
        date hire_date
        int salary
        float commission_pct
    }
    
    DEPARTMENT {
        int departmentID
        string department_name
    }
    
    JOB {
        int jobID
        string job_title
        int min_salary
        int max_salary
    }
    
    JOB_GRADE {
        string job_grade_level
        int lowest_sal
        int highest_sal
    }
    
    JOB_HISTORY {
        int jobID
        int departmentID
        date start_date
        date end_date
    }
    
    LOCATION {
        int locationID
        string street_address
        string postal_code
        string city
        string state_province
    }
    
    COUNTRY {
        string countryID
        string country_name
    }
    
    REGION {
        int regionID
        string region_name
    }
    
    EMPLOYEE ||--o{ DEPARTMENT : works_in
    DEPARTMENT ||--o{ LOCATION : located_in
    LOCATION ||--o{ COUNTRY : part_of
    COUNTRY ||--o{ REGION : part_of
    EMPLOYEE ||--|| JOB : has
    JOB ||--o{ JOB_GRADE : graded
    EMPLOYEE ||--o{ JOB_HISTORY : history

erDiagram
    EMPLOYEE {
        int employeeID
        string first_name
        string last_name
        string email
        string phone_number
        date hire_date
        int salary
        float commission_pct
    }
    
    DEPARTMENT {
        int departmentID
        string department_name
    }
    
    JOB {
        int jobID
        string job_title
        int min_salary
        int max_salary
    }
    
    JOB_GRADE {
        string job_grade_level
        int lowest_sal
        int highest_sal
    }
    
    JOB_HISTORY {
        int jobID
        int departmentID
        date start_date
        date end_date
    }
    
    LOCATION {
        int locationID
        string street_address
        string postal_code
        string city
        string state_province
    }
    
    COUNTRY {
        string countryID
        string country_name
    }
    
    REGION {
        int regionID
        string region_name
    }
    
    EMPLOYEE ||--o{ DEPARTMENT : works_in
    DEPARTMENT ||--o{ LOCATION : located_in
    LOCATION ||--o{ COUNTRY : part_of
    COUNTRY ||--o{ REGION : part_of
    EMPLOYEE ||--|| JOB : has
    JOB ||--o{ JOB_GRADE : graded
    EMPLOYEE ||--o{ JOB_HISTORY : history

Listing of tables in the database

The following python blocks highlight the steps used to document the tables in the database. The code is left in the report to show how fun it was to write python!

Create connection

Creating a connection to the “HR” database. This section of code:

  1. Interprets the .env file to locate each key
  2. Imports the necessary helper packages
  3. Saves each of the keys to the config_map
  4. Uses the config_map as a key to set up the server using its 4 variables

Feel free to explore the python in the helpers.py file.

from helpers import create_database_engine, run_sql_and_return_df, run_sql_and_return_html, create_db_wrapper
# load credentials from .env file
# load_dotenv()
# Load these variables from first .env file found.
config_map = {
  'user': "CMSC408_HW5_USER",
  'password': "CMSC408_HW5_PASSWORD",
  'host': "CMSC408_HW5_HOST",
  'database': "CMSC408_HW5_DB_NAME"
}

cnx, config = create_db_wrapper( config_map )

List of tables found in HR database

We took this piece of code from explore.qmd, which selects the table name and type using SQL from the information schema tables of our HR database. Printing each table!

run_sql_and_return_df(cnx,"""
SELECT 
  TABLE_NAME, TABLE_TYPE
FROM
  INFORMATION_SCHEMA.TABLES
WHERE
  TABLE_SCHEMA = 'sp25_torresc6_hr'
ORDER BY
  TABLE_NAME
""")
TABLE_NAME TABLE_TYPE
0 countries BASE TABLE
1 departments BASE TABLE
2 emp_details_view VIEW
3 employees BASE TABLE
4 job_grades BASE TABLE
5 job_history BASE TABLE
6 jobs BASE TABLE
7 locations BASE TABLE
8 regions BASE TABLE

Listing of Tables and sample data

The following sections provide a high-level view of the data found in the HR database.

Here, we not only print the tables, we also print their data! Similar to the code block above, we select table name, but this time we also select position, column name, and column type! This will help us display not only each table, but alos each column or subcategory as well!

For example, the Countries table has the ids of: country_id, country_name, and region_id, each with separate data under their respective columns! This code prints these column names!

run_sql_and_return_html(cnx,f"""
SELECT 
  TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  1=1
  AND TABLE_SCHEMA = '{config['database']}'
ORDER BY
  TABLE_NAME, ORDINAL_POSITION
""")
TABLE_NAME ORDINAL_POSITION COLUMN_NAME COLUMN_TYPE
countries 1 country_id char(2)
countries 2 country_name varchar(40)
countries 3 region_id int
departments 1 department_id int
departments 2 department_name varchar(30)
departments 3 manager_id int
departments 4 location_id int
emp_details_view 1 employee_id int
emp_details_view 2 job_id varchar(10)
emp_details_view 3 manager_id int
emp_details_view 4 department_id int
emp_details_view 5 location_id int
emp_details_view 6 country_id char(2)
emp_details_view 7 first_name varchar(20)
emp_details_view 8 last_name varchar(25)
emp_details_view 9 salary decimal(8,2)
emp_details_view 10 commission_pct decimal(2,2)
emp_details_view 11 department_name varchar(30)
emp_details_view 12 job_title varchar(35)
emp_details_view 13 city varchar(30)
emp_details_view 14 state_province varchar(25)
emp_details_view 15 country_name varchar(40)
emp_details_view 16 region_name varchar(25)
employees 1 employee_id int
employees 2 first_name varchar(20)
employees 3 last_name varchar(25)
employees 4 email varchar(25)
employees 5 phone_number varchar(20)
employees 6 hire_date date
employees 7 job_id varchar(10)
employees 8 salary decimal(8,2)
employees 9 commission_pct decimal(2,2)
employees 10 manager_id int
employees 11 department_id int
job_grades 1 grade_level varchar(3)
job_grades 2 lowest_sal float
job_grades 3 highest_sal float
job_history 1 employee_id int
job_history 2 start_date date
job_history 3 end_date date
job_history 4 job_id varchar(10)
job_history 5 department_id int
jobs 1 job_id varchar(10)
jobs 2 job_title varchar(35)
jobs 3 min_salary float
jobs 4 max_salary float
locations 1 location_id int
locations 2 street_address varchar(40)
locations 3 postal_code varchar(12)
locations 4 city varchar(30)
locations 5 state_province varchar(25)
locations 6 country_id char(2)
regions 1 region_id int
regions 2 region_name varchar(25)
Total Rows: 54, Total Columns: 4
run_sql_and_return_df(cnx,f"""
SELECT 
  TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  1=1
  AND TABLE_SCHEMA = '{config['database']}'
ORDER BY
  TABLE_NAME, ORDINAL_POSITION
""")
TABLE_NAME ORDINAL_POSITION COLUMN_NAME COLUMN_TYPE
0 countries 1 country_id char(2)
1 countries 2 country_name varchar(40)
2 countries 3 region_id int
3 departments 1 department_id int
4 departments 2 department_name varchar(30)
5 departments 3 manager_id int
6 departments 4 location_id int
7 emp_details_view 1 employee_id int
8 emp_details_view 2 job_id varchar(10)
9 emp_details_view 3 manager_id int
10 emp_details_view 4 department_id int
11 emp_details_view 5 location_id int
12 emp_details_view 6 country_id char(2)
13 emp_details_view 7 first_name varchar(20)
14 emp_details_view 8 last_name varchar(25)
15 emp_details_view 9 salary decimal(8,2)
16 emp_details_view 10 commission_pct decimal(2,2)
17 emp_details_view 11 department_name varchar(30)
18 emp_details_view 12 job_title varchar(35)
19 emp_details_view 13 city varchar(30)
20 emp_details_view 14 state_province varchar(25)
21 emp_details_view 15 country_name varchar(40)
22 emp_details_view 16 region_name varchar(25)
23 employees 1 employee_id int
24 employees 2 first_name varchar(20)
25 employees 3 last_name varchar(25)
26 employees 4 email varchar(25)
27 employees 5 phone_number varchar(20)
28 employees 6 hire_date date
29 employees 7 job_id varchar(10)
30 employees 8 salary decimal(8,2)
31 employees 9 commission_pct decimal(2,2)
32 employees 10 manager_id int
33 employees 11 department_id int
34 job_grades 1 grade_level varchar(3)
35 job_grades 2 lowest_sal float
36 job_grades 3 highest_sal float
37 job_history 1 employee_id int
38 job_history 2 start_date date
39 job_history 3 end_date date
40 job_history 4 job_id varchar(10)
41 job_history 5 department_id int
42 jobs 1 job_id varchar(10)
43 jobs 2 job_title varchar(35)
44 jobs 3 min_salary float
45 jobs 4 max_salary float
46 locations 1 location_id int
47 locations 2 street_address varchar(40)
48 locations 3 postal_code varchar(12)
49 locations 4 city varchar(30)
50 locations 5 state_province varchar(25)
51 locations 6 country_id char(2)
52 regions 1 region_id int
53 regions 2 region_name varchar(25)

Table: countries

The counties table contains data about the locations of the various offices and departments of the company.

Schema: countries

run_sql_and_return_html(cnx,f"""
SELECT 
        COLUMN_NAME, 
        DATA_TYPE, 
        CHARACTER_MAXIMUM_LENGTH, 
        IS_NULLABLE,
        COLUMN_KEY
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'countries' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE COLUMN_KEY
country_id char 2.0 NO PRI
country_name varchar 40.0 YES
region_id int NULL YES MUL
Total Rows: 3, Total Columns: 5

Sample data: countries

run_sql_and_return_html(cnx,f"""
SELECT
        country_id,
        country_name,
        region_id
    FROM
        countries;
""")
country_id country_name region_id
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
CA Canada 2
CH Switzerland 1
CN China 3
DE Germany 1
DK Denmark 1
EG Egypt 4
FR France 1
IL Israel 4
IN India 3
IT Italy 1
JP Japan 3
KW Kuwait 4
ML Malaysia 3
MX Mexico 2
NG Nigeria 4
NL Netherlands 1
SG Singapore 3
UK United Kingdom 1
US United States of America 2
ZM Zambia 4
ZW Zimbabwe 4
Total Rows: 25, Total Columns: 3

Table: departments

The departments table contains …

Schema: departments

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'departments' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE COLUMN_KEY
department_id int NULL NO PRI
department_name varchar 30.0 NO MUL
manager_id int NULL YES MUL
location_id int NULL YES MUL
Total Rows: 4, Total Columns: 5

Sample data: departments

run_sql_and_return_html(cnx,f"""
SELECT 

department_id, department_name, manager_id, location_id 

FROM

departments;
""")
department_id department_name manager_id location_id
10 Administration 200.0 1700
20 Marketing 201.0 1800
30 Purchasing 114.0 1700
40 Human Resources 203.0 2400
50 Shipping 121.0 1500
60 IT 103.0 1400
70 Public Relations 204.0 2700
80 Sales 145.0 2500
90 Executive 100.0 1700
100 Finance 108.0 1700
110 Accounting 205.0 1700
120 Treasury NULL 1700
130 Corporate Tax NULL 1700
140 Control And Credit NULL 1700
150 Shareholder Services NULL 1700
160 Benefits NULL 1700
170 Manufacturing NULL 1700
180 Construction NULL 1700
190 Contracting NULL 1700
200 Operations NULL 1700
210 IT Support NULL 1700
220 NOC NULL 1700
230 IT Helpdesk NULL 1700
240 Government Sales NULL 1700
250 Retail Sales NULL 1700
260 Recruiting NULL 1700
270 Payroll NULL 1700
Total Rows: 27, Total Columns: 4

Table: employees

The employees table contains …

Schema: employees

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'employees' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE COLUMN_KEY
employee_id int NULL NO PRI
first_name varchar 20.0 YES
last_name varchar 25.0 NO MUL
email varchar 25.0 NO UNI
phone_number varchar 20.0 YES
hire_date date NULL NO
job_id varchar 10.0 NO MUL
salary decimal NULL YES
commission_pct decimal NULL YES
manager_id int NULL YES MUL
department_id int NULL YES MUL
Total Rows: 11, Total Columns: 5

Sample data: employees

run_sql_and_return_html(cnx,f"""
SELECT 

employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id 

FROM employees;
""")
employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id
100 Steven King SKING 515.123.4567 2003-06-17 AD_PRES 24000.0 NULL NULL 90.0
101 Neena Kochhar NKOCHHAR 515.123.4568 2005-09-21 AD_VP 17000.0 NULL 100.0 90.0
102 Lex De Haan LDEHAAN 515.123.4569 2001-01-13 AD_VP 17000.0 NULL 100.0 90.0
103 Alexander Hunold AHUNOLD 590.423.4567 2006-01-03 IT_PROG 9000.0 NULL 102.0 60.0
104 Bruce Ernst BERNST 590.423.4568 2007-05-21 IT_PROG 6000.0 NULL 103.0 60.0
105 David Austin DAUSTIN 590.423.4569 2005-06-25 IT_PROG 4800.0 NULL 103.0 60.0
106 Valli Pataballa VPATABAL 590.423.4560 2006-02-05 IT_PROG 4800.0 NULL 103.0 60.0
107 Diana Lorentz DLORENTZ 590.423.5567 2007-02-07 IT_PROG 4200.0 NULL 103.0 60.0
108 Nancy Greenberg NGREENBE 515.124.4569 2002-08-17 FI_MGR 12008.0 NULL 101.0 100.0
109 Daniel Faviet DFAVIET 515.124.4169 2002-08-16 FI_ACCOUNT 9000.0 NULL 108.0 100.0
110 John Chen JCHEN 515.124.4269 2005-09-28 FI_ACCOUNT 8200.0 NULL 108.0 100.0
111 Ismael Sciarra ISCIARRA 515.124.4369 2005-09-30 FI_ACCOUNT 7700.0 NULL 108.0 100.0
112 Jose Manuel Urman JMURMAN 515.124.4469 2006-03-07 FI_ACCOUNT 7800.0 NULL 108.0 100.0
113 Luis Popp LPOPP 515.124.4567 2007-12-07 FI_ACCOUNT 6900.0 NULL 108.0 100.0
114 Den Raphaely DRAPHEAL 515.127.4561 2002-12-07 PU_MAN 11000.0 NULL 100.0 30.0
115 Alexander Khoo AKHOO 515.127.4562 2003-05-18 PU_CLERK 3100.0 NULL 114.0 30.0
116 Shelli Baida SBAIDA 515.127.4563 2005-12-24 PU_CLERK 2900.0 NULL 114.0 30.0
117 Sigal Tobias STOBIAS 515.127.4564 2005-07-24 PU_CLERK 2800.0 NULL 114.0 30.0
118 Guy Himuro GHIMURO 515.127.4565 2006-11-15 PU_CLERK 2600.0 NULL 114.0 30.0
119 Karen Colmenares KCOLMENA 515.127.4566 2007-08-10 PU_CLERK 2500.0 NULL 114.0 30.0
120 Matthew Weiss MWEISS 650.123.1234 2004-07-18 ST_MAN 8000.0 NULL 100.0 50.0
121 Adam Fripp AFRIPP 650.123.2234 2005-04-10 ST_MAN 8200.0 NULL 100.0 50.0
122 Payam Kaufling PKAUFLIN 650.123.3234 2003-05-01 ST_MAN 7900.0 NULL 100.0 50.0
123 Shanta Vollman SVOLLMAN 650.123.4234 2005-10-10 ST_MAN 6500.0 NULL 100.0 50.0
124 Kevin Mourgos KMOURGOS 650.123.5234 2007-11-16 ST_MAN 5800.0 NULL 100.0 50.0
125 Julia Nayer JNAYER 650.124.1214 2005-07-16 ST_CLERK 3200.0 NULL 120.0 50.0
126 Irene Mikkilineni IMIKKILI 650.124.1224 2006-09-28 ST_CLERK 2700.0 NULL 120.0 50.0
127 James Landry JLANDRY 650.124.1334 2007-01-14 ST_CLERK 2400.0 NULL 120.0 50.0
128 Steven Markle SMARKLE 650.124.1434 2008-03-08 ST_CLERK 2200.0 NULL 120.0 50.0
129 Laura Bissot LBISSOT 650.124.5234 2005-08-20 ST_CLERK 3300.0 NULL 121.0 50.0
130 Mozhe Atkinson MATKINSO 650.124.6234 2005-10-30 ST_CLERK 2800.0 NULL 121.0 50.0
131 James Marlow JAMRLOW 650.124.7234 2005-02-16 ST_CLERK 2500.0 NULL 121.0 50.0
132 TJ Olson TJOLSON 650.124.8234 2007-04-10 ST_CLERK 2100.0 NULL 121.0 50.0
133 Jason Mallin JMALLIN 650.127.1934 2004-06-14 ST_CLERK 3300.0 NULL 122.0 50.0
134 Michael Rogers MROGERS 650.127.1834 2006-08-26 ST_CLERK 2900.0 NULL 122.0 50.0
135 Ki Gee KGEE 650.127.1734 2007-12-12 ST_CLERK 2400.0 NULL 122.0 50.0
136 Hazel Philtanker HPHILTAN 650.127.1634 2008-02-06 ST_CLERK 2200.0 NULL 122.0 50.0
137 Renske Ladwig RLADWIG 650.121.1234 2003-07-14 ST_CLERK 3600.0 NULL 123.0 50.0
138 Stephen Stiles SSTILES 650.121.2034 2005-10-26 ST_CLERK 3200.0 NULL 123.0 50.0
139 John Seo JSEO 650.121.2019 2006-02-12 ST_CLERK 2700.0 NULL 123.0 50.0
140 Joshua Patel JPATEL 650.121.1834 2006-04-06 ST_CLERK 2500.0 NULL 123.0 50.0
141 Trenna Rajs TRAJS 650.121.8009 2003-10-17 ST_CLERK 3500.0 NULL 124.0 50.0
142 Curtis Davies CDAVIES 650.121.2994 2005-01-29 ST_CLERK 3100.0 NULL 124.0 50.0
143 Randall Matos RMATOS 650.121.2874 2006-03-15 ST_CLERK 2600.0 NULL 124.0 50.0
144 Peter Vargas PVARGAS 650.121.2004 2006-07-09 ST_CLERK 2500.0 NULL 124.0 50.0
145 John Russell JRUSSEL 011.44.1344.429268 2004-10-01 SA_MAN 14000.0 0.40 100.0 80.0
146 Karen Partners KPARTNER 011.44.1344.467268 2005-01-05 SA_MAN 13500.0 0.30 100.0 80.0
147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 2005-03-10 SA_MAN 12000.0 0.30 100.0 80.0
148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 2007-10-15 SA_MAN 11000.0 0.30 100.0 80.0
149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 2008-01-29 SA_MAN 10500.0 0.20 100.0 80.0
150 Peter Tucker PTUCKER 011.44.1344.129268 2005-01-30 SA_REP 10000.0 0.30 145.0 80.0
151 David Bernstein DBERNSTE 011.44.1344.345268 2005-03-24 SA_REP 9500.0 0.25 145.0 80.0
152 Peter Hall PHALL 011.44.1344.478968 2005-08-20 SA_REP 9000.0 0.25 145.0 80.0
153 Christopher Olsen COLSEN 011.44.1344.498718 2006-03-30 SA_REP 8000.0 0.20 145.0 80.0
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 2006-12-09 SA_REP 7500.0 0.20 145.0 80.0
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 2007-11-23 SA_REP 7000.0 0.15 145.0 80.0
156 Janette King JKING 011.44.1345.429268 2004-01-30 SA_REP 10000.0 0.35 146.0 80.0
157 Patrick Sully PSULLY 011.44.1345.929268 2004-03-04 SA_REP 9500.0 0.35 146.0 80.0
158 Allan McEwen AMCEWEN 011.44.1345.829268 2004-08-01 SA_REP 9000.0 0.35 146.0 80.0
159 Lindsey Smith LSMITH 011.44.1345.729268 2005-03-10 SA_REP 8000.0 0.30 146.0 80.0
160 Louise Doran LDORAN 011.44.1345.629268 2005-12-15 SA_REP 7500.0 0.30 146.0 80.0
161 Sarath Sewall SSEWALL 011.44.1345.529268 2006-11-03 SA_REP 7000.0 0.25 146.0 80.0
162 Clara Vishney CVISHNEY 011.44.1346.129268 2005-11-11 SA_REP 10500.0 0.25 147.0 80.0
163 Danielle Greene DGREENE 011.44.1346.229268 2007-03-19 SA_REP 9500.0 0.15 147.0 80.0
164 Mattea Marvins MMARVINS 011.44.1346.329268 2008-01-24 SA_REP 7200.0 0.10 147.0 80.0
165 David Lee DLEE 011.44.1346.529268 2008-02-23 SA_REP 6800.0 0.10 147.0 80.0
166 Sundar Ande SANDE 011.44.1346.629268 2008-03-24 SA_REP 6400.0 0.10 147.0 80.0
167 Amit Banda ABANDA 011.44.1346.729268 2008-04-21 SA_REP 6200.0 0.10 147.0 80.0
168 Lisa Ozer LOZER 011.44.1343.929268 2005-03-11 SA_REP 11500.0 0.25 148.0 80.0
169 Harrison Bloom HBLOOM 011.44.1343.829268 2006-03-23 SA_REP 10000.0 0.20 148.0 80.0
170 Tayler Fox TFOX 011.44.1343.729268 2006-01-24 SA_REP 9600.0 0.20 148.0 80.0
171 William Smith WSMITH 011.44.1343.629268 2007-02-23 SA_REP 7400.0 0.15 148.0 80.0
172 Elizabeth Bates EBATES 011.44.1343.529268 2007-03-24 SA_REP 7300.0 0.15 148.0 80.0
173 Sundita Kumar SKUMAR 011.44.1343.329268 2008-04-21 SA_REP 6100.0 0.10 148.0 80.0
174 Ellen Abel EABEL 011.44.1644.429267 2004-05-11 SA_REP 11000.0 0.30 149.0 80.0
175 Alyssa Hutton AHUTTON 011.44.1644.429266 2005-03-19 SA_REP 8800.0 0.25 149.0 80.0
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 2006-03-24 SA_REP 8600.0 0.20 149.0 80.0
177 Jack Livingston JLIVINGS 011.44.1644.429264 2006-04-23 SA_REP 8400.0 0.20 149.0 80.0
178 Kimberely Grant KGRANT 011.44.1644.429263 2007-05-24 SA_REP 7000.0 0.15 149.0 NULL
179 Charles Johnson CJOHNSON 011.44.1644.429262 2008-01-04 SA_REP 6200.0 0.10 149.0 80.0
180 Winston Taylor WTAYLOR 650.507.9876 2006-01-24 SH_CLERK 3200.0 NULL 120.0 50.0
181 Jean Fleaur JFLEAUR 650.507.9877 2006-02-23 SH_CLERK 3100.0 NULL 120.0 50.0
182 Martha Sullivan MSULLIVA 650.507.9878 2007-06-21 SH_CLERK 2500.0 NULL 120.0 50.0
183 Girard Geoni GGEONI 650.507.9879 2008-02-03 SH_CLERK 2800.0 NULL 120.0 50.0
184 Nandita Sarchand NSARCHAN 650.509.1876 2004-01-27 SH_CLERK 4200.0 NULL 121.0 50.0
185 Alexis Bull ABULL 650.509.2876 2005-02-20 SH_CLERK 4100.0 NULL 121.0 50.0
186 Julia Dellinger JDELLING 650.509.3876 2006-06-24 SH_CLERK 3400.0 NULL 121.0 50.0
187 Anthony Cabrio ACABRIO 650.509.4876 2007-02-07 SH_CLERK 3000.0 NULL 121.0 50.0
188 Kelly Chung KCHUNG 650.505.1876 2005-06-14 SH_CLERK 3800.0 NULL 122.0 50.0
189 Jennifer Dilly JDILLY 650.505.2876 2005-08-13 SH_CLERK 3600.0 NULL 122.0 50.0
190 Timothy Gates TGATES 650.505.3876 2006-07-11 SH_CLERK 2900.0 NULL 122.0 50.0
191 Randall Perkins RPERKINS 650.505.4876 2007-12-19 SH_CLERK 2500.0 NULL 122.0 50.0
192 Sarah Bell SBELL 650.501.1876 2004-02-04 SH_CLERK 4000.0 NULL 123.0 50.0
193 Britney Everett BEVERETT 650.501.2876 2005-03-03 SH_CLERK 3900.0 NULL 123.0 50.0
194 Samuel McCain SMCCAIN 650.501.3876 2006-07-01 SH_CLERK 3200.0 NULL 123.0 50.0
195 Vance Jones VJONES 650.501.4876 2007-03-17 SH_CLERK 2800.0 NULL 123.0 50.0
196 Alana Walsh AWALSH 650.507.9811 2006-04-24 SH_CLERK 3100.0 NULL 124.0 50.0
197 Kevin Feeney KFEENEY 650.507.9822 2006-05-23 SH_CLERK 3000.0 NULL 124.0 50.0
198 Donald OConnell DOCONNEL 650.507.9833 2007-06-21 SH_CLERK 2600.0 NULL 124.0 50.0
199 Douglas Grant DGRANT 650.507.9844 2008-01-13 SH_CLERK 2600.0 NULL 124.0 50.0
200 Jennifer Whalen JWHALEN 515.123.4444 2003-09-17 AD_ASST 4400.0 NULL 101.0 10.0
201 Michael Hartstein MHARTSTE 515.123.5555 2004-02-17 MK_MAN 13000.0 NULL 100.0 20.0
202 Pat Fay PFAY 603.123.6666 2005-08-17 MK_REP 6000.0 NULL 201.0 20.0
203 Susan Mavris SMAVRIS 515.123.7777 2002-06-07 HR_REP 6500.0 NULL 101.0 40.0
204 Hermann Baer HBAER 515.123.8888 2002-06-07 PR_REP 10000.0 NULL 101.0 70.0
205 Shelley Higgins SHIGGINS 515.123.8080 2002-06-07 AC_MGR 12008.0 NULL 101.0 110.0
206 William Gietz WGIETZ 515.123.8181 2002-06-07 AC_ACCOUNT 8300.0 NULL 205.0 110.0
Total Rows: 107, Total Columns: 11

Table: jobs

The jobs table contains …

Schema: jobs

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'jobs' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE COLUMN_KEY
job_id varchar 10.0 NO PRI
job_title varchar 35.0 NO
min_salary float NULL YES
max_salary float NULL YES
Total Rows: 4, Total Columns: 5

Sample data: jobs

run_sql_and_return_html(cnx,f"""
SELECT 

job_id, job_title, min_salary, max_salary 

FROM jobs;
""")
job_id job_title min_salary max_salary
AC_ACCOUNT Public Accountant 4200.0 9000.0
AC_MGR Accounting Manager 8200.0 16000.0
AD_ASST Administration Assistant 3000.0 6000.0
AD_PRES President 20080.0 40000.0
AD_VP Administration Vice President 15000.0 30000.0
FI_ACCOUNT Accountant 4200.0 9000.0
FI_MGR Finance Manager 8200.0 16000.0
HR_REP Human Resources Representative 4000.0 9000.0
IT_PROG Programmer 4000.0 10000.0
MK_MAN Marketing Manager 9000.0 15000.0
MK_REP Marketing Representative 4000.0 9000.0
PR_REP Public Relations Representative 4500.0 10500.0
PU_CLERK Purchasing Clerk 2500.0 5500.0
PU_MAN Purchasing Manager 8000.0 15000.0
SA_MAN Sales Manager 10000.0 20080.0
SA_REP Sales Representative 6000.0 12008.0
SH_CLERK Shipping Clerk 2500.0 5500.0
ST_CLERK Stock Clerk 2008.0 5000.0
ST_MAN Stock Manager 5500.0 8500.0
Total Rows: 19, Total Columns: 4

Table: job_grades

The job_grades table contains …

Schema: job_grades

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'job_grades' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE COLUMN_KEY
grade_level varchar 3.0 NO PRI
lowest_sal float NULL YES
highest_sal float NULL YES
Total Rows: 3, Total Columns: 5

Sample data: job_grades

run_sql_and_return_html(cnx,f"""
SELECT 

grade_level, lowest_sal, highest_sal

FROM job_grades;
""")
grade_level lowest_sal highest_sal
A 1000.0 2999.0
B 3000.0 5999.0
C 6000.0 9999.0
D 10000.0 14999.0
E 15000.0 24999.0
F 25000.0 40000.0
Total Rows: 6, Total Columns: 3

Table: job_history

The job_history table contains …

Schema: job_history

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'job_history' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE COLUMN_KEY
employee_id int NULL NO PRI
start_date date NULL NO PRI
end_date date NULL NO PRI
job_id varchar 10.0 NO MUL
department_id int NULL YES MUL
Total Rows: 5, Total Columns: 5

Sample data: job_history

run_sql_and_return_html(cnx,f"""
SELECT 

employee_id, start_date, end_date, job_id, department_id
 
FROM job_history;
""")
employee_id start_date end_date job_id department_id
101 1997-09-21 2001-10-27 AC_ACCOUNT 110
101 2001-10-28 2005-03-15 AC_MGR 110
102 2001-01-13 2006-07-24 IT_PROG 60
114 2006-03-24 2007-12-31 ST_CLERK 50
122 2007-01-01 2007-12-31 ST_CLERK 50
176 2006-03-24 2006-12-31 SA_REP 80
176 2007-01-01 2007-12-31 SA_MAN 80
200 1995-09-17 2001-06-17 AD_ASST 90
200 2002-07-01 2006-12-31 AC_ACCOUNT 90
201 2004-02-17 2007-12-19 MK_REP 20
Total Rows: 10, Total Columns: 5

Table: locations

The locations table contains …

Schema: locations

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'locations' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE COLUMN_KEY
location_id int NULL NO PRI
street_address varchar 40.0 YES
postal_code varchar 12.0 YES
city varchar 30.0 NO MUL
state_province varchar 25.0 YES MUL
country_id char 2.0 YES MUL
Total Rows: 6, Total Columns: 5

Sample data: locations

run_sql_and_return_html(cnx,f"""
SELECT 

location_id, street_address, postal_code, city, state_province, country_id 
 
FROM locations;
""")
location_id street_address postal_code city state_province country_id
1000 1297 Via Cola di Rie 00989 Roma NULL IT
1100 93091 Calle della Testa 10934 Venice NULL IT
1200 2017 Shinjuku-ku 1689 Tokyo Tokyo Prefecture JP
1300 9450 Kamiya-cho 6823 Hiroshima NULL JP
1400 2014 Jabberwocky Rd 26192 Southlake Texas US
1500 2011 Interiors Blvd 99236 South San Francisco California US
1600 2007 Zagora St 50090 South Brunswick New Jersey US
1700 2004 Charade Rd 98199 Seattle Washington US
1800 147 Spadina Ave M5V 2L7 Toronto Ontario CA
1900 6092 Boxwood St YSW 9T2 Whitehorse Yukon CA
2000 40-5-12 Laogianggen 190518 Beijing NULL CN
2100 1298 Vileparle (E) 490231 Bombay Maharashtra IN
2200 12-98 Victoria Street 2901 Sydney New South Wales AU
2300 198 Clementi North 540198 Singapore NULL SG
2400 8204 Arthur St NULL London NULL UK
2500 Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford Oxford UK
2600 9702 Chester Road 09629850293 Stretford Manchester UK
2700 Schwanthalerstr. 7031 80925 Munich Bavaria DE
2800 Rua Frei Caneca 1360 01307-002 Sao Paulo Sao Paulo BR
2900 20 Rue des Corps-Saints 1730 Geneva Geneve CH
3000 Murtenstrasse 921 3095 Bern BE CH
3100 Pieter Breughelstraat 837 3029SK Utrecht Utrecht NL
3200 Mariano Escobedo 9991 11932 Mexico City Distrito Federal, MX
Total Rows: 23, Total Columns: 6

Table: regions

The regions table contains …

Schema: regions

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'regions' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE COLUMN_KEY
region_id int NULL NO PRI
region_name varchar 25.0 YES
Total Rows: 2, Total Columns: 5

Sample data: regions

run_sql_and_return_html(cnx,f"""
SELECT 

region_id, region_name
 
FROM regions;
""")
region_id region_name
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
Total Rows: 4, Total Columns: 2

Table: emp_details_view

The emp_details_view table contains …

Schema: emp_details_view

run_sql_and_return_html(cnx,f"""
SELECT 
            COLUMN_NAME, 
            DATA_TYPE, 
            CHARACTER_MAXIMUM_LENGTH, 
            IS_NULLABLE,
            COLUMN_KEY
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'emp_details_view' AND TABLE_SCHEMA = 'sp25_torresc6_hr';
""")
COLUMN_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH IS_NULLABLE COLUMN_KEY
employee_id int NULL NO
job_id varchar 10.0 NO
manager_id int NULL YES
department_id int NULL YES
location_id int NULL YES
country_id char 2.0 YES
first_name varchar 20.0 YES
last_name varchar 25.0 NO
salary decimal NULL YES
commission_pct decimal NULL YES
department_name varchar 30.0 NO
job_title varchar 35.0 NO
city varchar 30.0 NO
state_province varchar 25.0 YES
country_name varchar 40.0 YES
region_name varchar 25.0 YES
Total Rows: 16, Total Columns: 5

Sample data: emp_details_view

run_sql_and_return_html(cnx,f"""
SELECT 

employee_id, job_id, manager_id, department_id, location_id, country_id, first_name, last_name, salary, commission_pct, department_name, job_title, city, state_province, country_name, region_name 

FROM

emp_details_view

""")
employee_id job_id manager_id department_id location_id country_id first_name last_name salary commission_pct department_name job_title city state_province country_name region_name
200 AD_ASST 101.0 10 1700 US Jennifer Whalen 4400.0 NULL Administration Administration Assistant Seattle Washington United States of America Americas
201 MK_MAN 100.0 20 1800 CA Michael Hartstein 13000.0 NULL Marketing Marketing Manager Toronto Ontario Canada Americas
202 MK_REP 201.0 20 1800 CA Pat Fay 6000.0 NULL Marketing Marketing Representative Toronto Ontario Canada Americas
114 PU_MAN 100.0 30 1700 US Den Raphaely 11000.0 NULL Purchasing Purchasing Manager Seattle Washington United States of America Americas
115 PU_CLERK 114.0 30 1700 US Alexander Khoo 3100.0 NULL Purchasing Purchasing Clerk Seattle Washington United States of America Americas
116 PU_CLERK 114.0 30 1700 US Shelli Baida 2900.0 NULL Purchasing Purchasing Clerk Seattle Washington United States of America Americas
117 PU_CLERK 114.0 30 1700 US Sigal Tobias 2800.0 NULL Purchasing Purchasing Clerk Seattle Washington United States of America Americas
118 PU_CLERK 114.0 30 1700 US Guy Himuro 2600.0 NULL Purchasing Purchasing Clerk Seattle Washington United States of America Americas
119 PU_CLERK 114.0 30 1700 US Karen Colmenares 2500.0 NULL Purchasing Purchasing Clerk Seattle Washington United States of America Americas
203 HR_REP 101.0 40 2400 UK Susan Mavris 6500.0 NULL Human Resources Human Resources Representative London NULL United Kingdom Europe
120 ST_MAN 100.0 50 1500 US Matthew Weiss 8000.0 NULL Shipping Stock Manager South San Francisco California United States of America Americas
121 ST_MAN 100.0 50 1500 US Adam Fripp 8200.0 NULL Shipping Stock Manager South San Francisco California United States of America Americas
122 ST_MAN 100.0 50 1500 US Payam Kaufling 7900.0 NULL Shipping Stock Manager South San Francisco California United States of America Americas
123 ST_MAN 100.0 50 1500 US Shanta Vollman 6500.0 NULL Shipping Stock Manager South San Francisco California United States of America Americas
124 ST_MAN 100.0 50 1500 US Kevin Mourgos 5800.0 NULL Shipping Stock Manager South San Francisco California United States of America Americas
125 ST_CLERK 120.0 50 1500 US Julia Nayer 3200.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
126 ST_CLERK 120.0 50 1500 US Irene Mikkilineni 2700.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
127 ST_CLERK 120.0 50 1500 US James Landry 2400.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
128 ST_CLERK 120.0 50 1500 US Steven Markle 2200.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
129 ST_CLERK 121.0 50 1500 US Laura Bissot 3300.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
130 ST_CLERK 121.0 50 1500 US Mozhe Atkinson 2800.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
131 ST_CLERK 121.0 50 1500 US James Marlow 2500.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
132 ST_CLERK 121.0 50 1500 US TJ Olson 2100.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
133 ST_CLERK 122.0 50 1500 US Jason Mallin 3300.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
134 ST_CLERK 122.0 50 1500 US Michael Rogers 2900.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
135 ST_CLERK 122.0 50 1500 US Ki Gee 2400.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
136 ST_CLERK 122.0 50 1500 US Hazel Philtanker 2200.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
137 ST_CLERK 123.0 50 1500 US Renske Ladwig 3600.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
138 ST_CLERK 123.0 50 1500 US Stephen Stiles 3200.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
139 ST_CLERK 123.0 50 1500 US John Seo 2700.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
140 ST_CLERK 123.0 50 1500 US Joshua Patel 2500.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
141 ST_CLERK 124.0 50 1500 US Trenna Rajs 3500.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
142 ST_CLERK 124.0 50 1500 US Curtis Davies 3100.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
143 ST_CLERK 124.0 50 1500 US Randall Matos 2600.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
144 ST_CLERK 124.0 50 1500 US Peter Vargas 2500.0 NULL Shipping Stock Clerk South San Francisco California United States of America Americas
180 SH_CLERK 120.0 50 1500 US Winston Taylor 3200.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
181 SH_CLERK 120.0 50 1500 US Jean Fleaur 3100.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
182 SH_CLERK 120.0 50 1500 US Martha Sullivan 2500.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
183 SH_CLERK 120.0 50 1500 US Girard Geoni 2800.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
184 SH_CLERK 121.0 50 1500 US Nandita Sarchand 4200.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
185 SH_CLERK 121.0 50 1500 US Alexis Bull 4100.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
186 SH_CLERK 121.0 50 1500 US Julia Dellinger 3400.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
187 SH_CLERK 121.0 50 1500 US Anthony Cabrio 3000.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
188 SH_CLERK 122.0 50 1500 US Kelly Chung 3800.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
189 SH_CLERK 122.0 50 1500 US Jennifer Dilly 3600.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
190 SH_CLERK 122.0 50 1500 US Timothy Gates 2900.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
191 SH_CLERK 122.0 50 1500 US Randall Perkins 2500.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
192 SH_CLERK 123.0 50 1500 US Sarah Bell 4000.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
193 SH_CLERK 123.0 50 1500 US Britney Everett 3900.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
194 SH_CLERK 123.0 50 1500 US Samuel McCain 3200.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
195 SH_CLERK 123.0 50 1500 US Vance Jones 2800.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
196 SH_CLERK 124.0 50 1500 US Alana Walsh 3100.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
197 SH_CLERK 124.0 50 1500 US Kevin Feeney 3000.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
198 SH_CLERK 124.0 50 1500 US Donald OConnell 2600.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
199 SH_CLERK 124.0 50 1500 US Douglas Grant 2600.0 NULL Shipping Shipping Clerk South San Francisco California United States of America Americas
103 IT_PROG 102.0 60 1400 US Alexander Hunold 9000.0 NULL IT Programmer Southlake Texas United States of America Americas
104 IT_PROG 103.0 60 1400 US Bruce Ernst 6000.0 NULL IT Programmer Southlake Texas United States of America Americas
105 IT_PROG 103.0 60 1400 US David Austin 4800.0 NULL IT Programmer Southlake Texas United States of America Americas
106 IT_PROG 103.0 60 1400 US Valli Pataballa 4800.0 NULL IT Programmer Southlake Texas United States of America Americas
107 IT_PROG 103.0 60 1400 US Diana Lorentz 4200.0 NULL IT Programmer Southlake Texas United States of America Americas
204 PR_REP 101.0 70 2700 DE Hermann Baer 10000.0 NULL Public Relations Public Relations Representative Munich Bavaria Germany Europe
145 SA_MAN 100.0 80 2500 UK John Russell 14000.0 0.40 Sales Sales Manager Oxford Oxford United Kingdom Europe
146 SA_MAN 100.0 80 2500 UK Karen Partners 13500.0 0.30 Sales Sales Manager Oxford Oxford United Kingdom Europe
147 SA_MAN 100.0 80 2500 UK Alberto Errazuriz 12000.0 0.30 Sales Sales Manager Oxford Oxford United Kingdom Europe
148 SA_MAN 100.0 80 2500 UK Gerald Cambrault 11000.0 0.30 Sales Sales Manager Oxford Oxford United Kingdom Europe
149 SA_MAN 100.0 80 2500 UK Eleni Zlotkey 10500.0 0.20 Sales Sales Manager Oxford Oxford United Kingdom Europe
150 SA_REP 145.0 80 2500 UK Peter Tucker 10000.0 0.30 Sales Sales Representative Oxford Oxford United Kingdom Europe
151 SA_REP 145.0 80 2500 UK David Bernstein 9500.0 0.25 Sales Sales Representative Oxford Oxford United Kingdom Europe
152 SA_REP 145.0 80 2500 UK Peter Hall 9000.0 0.25 Sales Sales Representative Oxford Oxford United Kingdom Europe
153 SA_REP 145.0 80 2500 UK Christopher Olsen 8000.0 0.20 Sales Sales Representative Oxford Oxford United Kingdom Europe
154 SA_REP 145.0 80 2500 UK Nanette Cambrault 7500.0 0.20 Sales Sales Representative Oxford Oxford United Kingdom Europe
155 SA_REP 145.0 80 2500 UK Oliver Tuvault 7000.0 0.15 Sales Sales Representative Oxford Oxford United Kingdom Europe
156 SA_REP 146.0 80 2500 UK Janette King 10000.0 0.35 Sales Sales Representative Oxford Oxford United Kingdom Europe
157 SA_REP 146.0 80 2500 UK Patrick Sully 9500.0 0.35 Sales Sales Representative Oxford Oxford United Kingdom Europe
158 SA_REP 146.0 80 2500 UK Allan McEwen 9000.0 0.35 Sales Sales Representative Oxford Oxford United Kingdom Europe
159 SA_REP 146.0 80 2500 UK Lindsey Smith 8000.0 0.30 Sales Sales Representative Oxford Oxford United Kingdom Europe
160 SA_REP 146.0 80 2500 UK Louise Doran 7500.0 0.30 Sales Sales Representative Oxford Oxford United Kingdom Europe
161 SA_REP 146.0 80 2500 UK Sarath Sewall 7000.0 0.25 Sales Sales Representative Oxford Oxford United Kingdom Europe
162 SA_REP 147.0 80 2500 UK Clara Vishney 10500.0 0.25 Sales Sales Representative Oxford Oxford United Kingdom Europe
163 SA_REP 147.0 80 2500 UK Danielle Greene 9500.0 0.15 Sales Sales Representative Oxford Oxford United Kingdom Europe
164 SA_REP 147.0 80 2500 UK Mattea Marvins 7200.0 0.10 Sales Sales Representative Oxford Oxford United Kingdom Europe
165 SA_REP 147.0 80 2500 UK David Lee 6800.0 0.10 Sales Sales Representative Oxford Oxford United Kingdom Europe
166 SA_REP 147.0 80 2500 UK Sundar Ande 6400.0 0.10 Sales Sales Representative Oxford Oxford United Kingdom Europe
167 SA_REP 147.0 80 2500 UK Amit Banda 6200.0 0.10 Sales Sales Representative Oxford Oxford United Kingdom Europe
168 SA_REP 148.0 80 2500 UK Lisa Ozer 11500.0 0.25 Sales Sales Representative Oxford Oxford United Kingdom Europe
169 SA_REP 148.0 80 2500 UK Harrison Bloom 10000.0 0.20 Sales Sales Representative Oxford Oxford United Kingdom Europe
170 SA_REP 148.0 80 2500 UK Tayler Fox 9600.0 0.20 Sales Sales Representative Oxford Oxford United Kingdom Europe
171 SA_REP 148.0 80 2500 UK William Smith 7400.0 0.15 Sales Sales Representative Oxford Oxford United Kingdom Europe
172 SA_REP 148.0 80 2500 UK Elizabeth Bates 7300.0 0.15 Sales Sales Representative Oxford Oxford United Kingdom Europe
173 SA_REP 148.0 80 2500 UK Sundita Kumar 6100.0 0.10 Sales Sales Representative Oxford Oxford United Kingdom Europe
174 SA_REP 149.0 80 2500 UK Ellen Abel 11000.0 0.30 Sales Sales Representative Oxford Oxford United Kingdom Europe
175 SA_REP 149.0 80 2500 UK Alyssa Hutton 8800.0 0.25 Sales Sales Representative Oxford Oxford United Kingdom Europe
176 SA_REP 149.0 80 2500 UK Jonathon Taylor 8600.0 0.20 Sales Sales Representative Oxford Oxford United Kingdom Europe
177 SA_REP 149.0 80 2500 UK Jack Livingston 8400.0 0.20 Sales Sales Representative Oxford Oxford United Kingdom Europe
179 SA_REP 149.0 80 2500 UK Charles Johnson 6200.0 0.10 Sales Sales Representative Oxford Oxford United Kingdom Europe
100 AD_PRES NULL 90 1700 US Steven King 24000.0 NULL Executive President Seattle Washington United States of America Americas
101 AD_VP 100.0 90 1700 US Neena Kochhar 17000.0 NULL Executive Administration Vice President Seattle Washington United States of America Americas
102 AD_VP 100.0 90 1700 US Lex De Haan 17000.0 NULL Executive Administration Vice President Seattle Washington United States of America Americas
108 FI_MGR 101.0 100 1700 US Nancy Greenberg 12008.0 NULL Finance Finance Manager Seattle Washington United States of America Americas
109 FI_ACCOUNT 108.0 100 1700 US Daniel Faviet 9000.0 NULL Finance Accountant Seattle Washington United States of America Americas
110 FI_ACCOUNT 108.0 100 1700 US John Chen 8200.0 NULL Finance Accountant Seattle Washington United States of America Americas
111 FI_ACCOUNT 108.0 100 1700 US Ismael Sciarra 7700.0 NULL Finance Accountant Seattle Washington United States of America Americas
112 FI_ACCOUNT 108.0 100 1700 US Jose Manuel Urman 7800.0 NULL Finance Accountant Seattle Washington United States of America Americas
113 FI_ACCOUNT 108.0 100 1700 US Luis Popp 6900.0 NULL Finance Accountant Seattle Washington United States of America Americas
205 AC_MGR 101.0 110 1700 US Shelley Higgins 12008.0 NULL Accounting Accounting Manager Seattle Washington United States of America Americas
206 AC_ACCOUNT 205.0 110 1700 US William Gietz 8300.0 NULL Accounting Public Accountant Seattle Washington United States of America Americas
Total Rows: 106, Total Columns: 16

Reflection

Please add a paragraph or two about how this project went.
At first it was really daunting, all of the installing. I have like computer science PTSD when it comes to making sure everything is installed the right way! It has always been one of the most frusturating parts for no reason! Making sure everything is installed correctly just for it to work is the last thing I should be worrying about! However, it does show a mastery in systems to be able to move through one efficiently and understand why it works the way it does. Having a mastery in
Was it harder or easier than you expected?
It was about as difficult as I expected. Professor said this assigment was going to be slightly trickier, so I took my time. It was relatively difficult, but I have finished all components. Just took a lot of debugging and time.
Did the instructor provide too much information or not enough information?
The instructor provided the perfect information! However, at the end, I still had to install a few more modules, and there was one grammatical error in helpers.py. These things could throw someone off, and it might send them back to the tutorial instead of realizing these errors are quick fixes. Maybe pandas is installed on other systems automatically though.
Do you have suggestions for how it can be improved?
As I said right above. There is one grammatical error in helpers.py and there could be a clarification that the user might need to download separately a few modules. I know we had to avoid using pip earlier, so all of this could be confusing. Otherwise, perfect instruction! The .env could also be mentioned as needing to be edited.

README

Homework 5 - CMSC408 - Spring 2025

This will be the first homework to involve SQL! We are going to installing necessary python packages to get SQL up and running while Quarto converts our code to HTML through VSCode! This will set us up for SQL to end the semester and it will give us the tools to design our own database using SQL!

Homework tasks

Together, we will:

  1. Install the necessary python tools and libraries

  2. Verify that the tools and libraries are installed.

  3. Verify that you can log into the phpMyAdmin site.

  4. Verify that your VSCODE can connect to the mySql server.

  5. Explore queries and result sets using pandas

  6. Explore Quarto as a tool for writing out tables.

  7. Explore and document a new database that some provided to you.

  8. Render explore.qmd to ensure that everything works.

  9. Complete the report.qmd scaffold and render the report!

Files and folders

All necessary files are stored in the ./reports folder.